/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/*
 * DBClientQuery.java
 *
 * Created on May 19, 2012, 3:09:36 AM
 */
package db.client.GUI;

import db.client.BUS.QueryBUS;
import db.client.Connection.ExecuteThread;
import db.client.DTO.EditTableDTO;
import db.client.DTO.QueryDTO;
import java.io.IOException;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.*;
import java.util.*;
import javax.swing.event.DocumentEvent;
import javax.swing.event.DocumentListener;
import java.awt.event.ActionEvent;
import java.awt.event.ItemEvent;
import java.awt.event.ItemListener;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.net.Socket;
import javax.swing.text.BadLocationException;
import javax.swing.table.DefaultTableModel;
import javax.xml.parsers.ParserConfigurationException;

/**
 *
 * @author QuangKhai
 */
public final class DBClientQuery extends javax.swing.JPanel implements DocumentListener{
    public JTabbedPane tabpane;    
    public QueryDTO queryDTO = new QueryDTO();
    private QueryBUS queryBUS = new QueryBUS();
    public List<String> databaseName = new ArrayList<String>();
    private DefaultTableModel tableResult = new DefaultTableModel();            
    public EditTableDTO editTableDTO = new EditTableDTO();
    public Socket socket;
    public ObjectOutputStream oos;
    public ObjectInputStream ois;   
    private Thread execute = new Thread();
    public String savePath = "";
    private List<List<String>> lstColumns = new ArrayList<List<String>>();
            
    private static final String COMMIT_ACTION = "commit";
    private static enum Mode { INSERT, COMPLETION };
    private List<String> words = new ArrayList<String>();
    private Mode mode = Mode.INSERT;
    private InputMap im;
    private ActionMap am;
    
    /** Creates new form DBClientQuery */
    public DBClientQuery(JTabbedPane tabpane, List<String> databaseName, Socket socket, ObjectOutputStream oos, ObjectInputStream ois, List<List<String>> lstColumns) {
        initComponents();
        this.tabpane = tabpane;            
        this.tableResult = (DefaultTableModel) resulttable.getModel();          
        this.databaseName = databaseName;
        this.socket = socket;
        this.oos = oos;
        this.ois = ois;
        this.lstColumns = lstColumns;
                
        setAuto();
    }
    
    ///Support call save query from outside
    public void save(){
        ActionEvent evt = null;
        bt_saveActionPerformed(evt);
    }   
    
    ///Support call execute query from outside
    public void execute(){
        ActionEvent evt = null;
        bt_excuteActionPerformed(evt);
    }
    
    public DBClientQuery(JTabbedPane tabpane, List<String> databaseName, Socket socket, ObjectOutputStream oos, ObjectInputStream ois, QueryDTO query, List<List<String>> lstColumns){
        initComponents();
        this.tabpane = tabpane;            
        this.tableResult = (DefaultTableModel) resulttable.getModel();          
        this.databaseName = databaseName;
        this.socket = socket;
        this.oos = oos;
        this.ois = ois;
        this.lstColumns = lstColumns;
        
        setAuto();
        showQueryFile(query);        
    }
    
    ///read query file to show on text area
    public void showQueryFile(QueryDTO query){
        for(int i = 0; i < databaseName.size(); i++){
            if(databaseName.get(i).compareTo(query.getDatabaseName()) == 0){
                cbb_database.setSelectedIndex(i);
            }
        }        
        
        for(int i = 0; i < query.getQuerySentences().size(); i++){
            sqlquerytext.append(query.getQuerySentences().get(i) + "\n");            
        }
    }

    ///Set up autocompete
    public void setAuto(){
        im = sqlquerytext.getInputMap();
        am = sqlquerytext.getActionMap();
        im.put(KeyStroke.getKeyStroke("ENTER"), COMMIT_ACTION);
        am.put(COMMIT_ACTION, new CommitAction());
        sqlquerytext.getDocument().addDocumentListener(this);
        
        for(int i = 0; i < databaseName.size(); i++){
            cbb_database.addItem(databaseName.get(i));
        }
        
        addWords();
        
        cbb_database.addItemListener(new ItemListener() { 
            public void itemStateChanged(ItemEvent e) {
                addWords();                
            }
        });
    }

    @Override
    public void insertUpdate(DocumentEvent e) {
        if (e.getLength() != 1) {
            return;
        }

        int pos = e.getOffset();
        String content = null;
        try {
            content = sqlquerytext.getText(0, pos + 1);
        } catch (BadLocationException ex) {
            Logger.getLogger(DBClientQuery.class.getName()).log(Level.SEVERE, null, ex);
        }
        

        // Find where the word starts
        int w;
        for (w = pos; w >= 0; w--) {
            if (! Character.isLetter(content.charAt(w))) {
                break;
            }
        }
        if (pos - w < 2) {
            // Too few chars
            return;
        }

        String prefix = content.substring(w + 1);

        int n = 0;
        for(int j = 0; j < words.size(); j++){
            if(words.get(j).contains(prefix)){
                n = -(j + 1);
                break;
            }
        }

        if (n < 0 && -n <= words.size()) {
            String match = words.get(-n - 1);
            if (match.startsWith(prefix)) {
                // A completion is found
                String completion = match.substring(pos - w);
                // We cannot modify Document from within notification,
                // so we submit a task that does the change later
                SwingUtilities.invokeLater(
                        new CompletionTask(completion, pos + 1));
            }
        } else {
            // Nothing found
            mode = Mode.INSERT;
        }
    }    
    
    public void removeUpdate(DocumentEvent e) {                
    }
    
    public void changedUpdate(DocumentEvent e) {                
    }
            
    ///Add key words
    public void addWords(){
        words.clear();
        
        words.add("SELECT ");
        words.add("FROM ");
        words.add("WHERE ");
        words.add("INSERT INTO ");
        words.add("UPDATE ");
        words.add("DELETE FROM ");        
        words.add("GROUP BY ");
        words.add("SUM ");
        words.add("MIN ");
        words.add("MAX ");
        words.add("AVG ");
        words.add("Count ");
        
        int index = cbb_database.getSelectedIndex();
        for(int i = 0; i < lstColumns.get(index).size(); i++){
            words.add(lstColumns.get(cbb_database.getSelectedIndex()).get(i));
        }
    }

    private class CommitAction extends AbstractAction {
        @Override
        public void actionPerformed(ActionEvent ev) {
            if (mode == Mode.COMPLETION) {
                int pos = sqlquerytext.getSelectionEnd();
                sqlquerytext.insert(" ", pos);
                sqlquerytext.setCaretPosition(pos);
                mode = Mode.INSERT;
            } else {
                sqlquerytext.replaceSelection("\n");
            }
        }
    }
    
    private class CompletionTask implements Runnable {
        String completion;
        int position;
        
        CompletionTask(String completion, int position) {
            this.completion = completion;
            this.position = position;
        }
        
        @Override
        public void run() {
            sqlquerytext.insert(completion, position);
            sqlquerytext.setCaretPosition(position + completion.length());
            sqlquerytext.moveCaretPosition(position);
            mode = Mode.COMPLETION;
        }
    }
    
    public void showResults(){
        for(int i = 0; i < editTableDTO.getColumnName().size(); i++){  
            tableResult.addColumn(editTableDTO.getColumnName().get(i));
        }
        
        int columns = editTableDTO.getColumnName().size();
        int rows = editTableDTO.getLstValues().size();
        
        for(int i = 0; i < rows; i++){
            tableResult.insertRow(resulttable.getRowCount(), new Object[]{});    
            for(int j = 0; j < columns; j++){                
                tableResult.setValueAt(editTableDTO.getLstValues().get(i).get(j), i, j);                                                
            }                
        }
        
        messagestext.setText(rows + " rows affected!");
    }
    /** This method is called from within the constructor to
     * initialize the form.
     * WARNING: Do NOT modify this code. The content of this method is
     * always regenerated by the Form Editor.
     */
    @SuppressWarnings("unchecked")
    // <editor-fold defaultstate="collapsed" desc="Generated Code">//GEN-BEGIN:initComponents
    private void initComponents() {

        jLabel1 = new javax.swing.JLabel();
        bt_excute = new javax.swing.JButton();
        bt_cancel = new javax.swing.JButton();
        cbb_database = new javax.swing.JComboBox();
        bt_save = new javax.swing.JButton();
        jScrollPane1 = new javax.swing.JScrollPane();
        sqlquerytext = new javax.swing.JTextArea();
        jTabbedPane1 = new javax.swing.JTabbedPane();
        jScrollPane2 = new javax.swing.JScrollPane();
        resulttable = new javax.swing.JTable();
        jScrollPane3 = new javax.swing.JScrollPane();
        messagestext = new javax.swing.JTextArea();

        jLabel1.setFont(new java.awt.Font("Cambria", 0, 12)); // NOI18N
        jLabel1.setText("Database");

        bt_excute.setFont(new java.awt.Font("Cambria", 0, 12)); // NOI18N
        bt_excute.setText("Excute");
        bt_excute.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                bt_excuteActionPerformed(evt);
            }
        });

        bt_cancel.setFont(new java.awt.Font("Cambria", 0, 12)); // NOI18N
        bt_cancel.setText("Cancel");
        bt_cancel.setFocusable(false);
        bt_cancel.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                bt_cancelActionPerformed(evt);
            }
        });

        cbb_database.setFont(new java.awt.Font("Cambria", 0, 12)); // NOI18N

        bt_save.setFont(new java.awt.Font("Cambria", 0, 12)); // NOI18N
        bt_save.setText("Save");
        bt_save.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                bt_saveActionPerformed(evt);
            }
        });

        sqlquerytext.setColumns(20);
        sqlquerytext.setLineWrap(true);
        sqlquerytext.setRows(5);
        sqlquerytext.setWrapStyleWord(true);
        sqlquerytext.setInheritsPopupMenu(true);
        jScrollPane1.setViewportView(sqlquerytext);

        resulttable.setModel(new javax.swing.table.DefaultTableModel(
            new Object [][] {

            },
            new String [] {

            }
        ));
        jScrollPane2.setViewportView(resulttable);

        jTabbedPane1.addTab("Results", jScrollPane2);

        messagestext.setColumns(20);
        messagestext.setRows(5);
        jScrollPane3.setViewportView(messagestext);

        jTabbedPane1.addTab("Messages", jScrollPane3);

        javax.swing.GroupLayout layout = new javax.swing.GroupLayout(this);
        this.setLayout(layout);
        layout.setHorizontalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addComponent(jTabbedPane1, javax.swing.GroupLayout.DEFAULT_SIZE, 498, Short.MAX_VALUE)
                    .addGroup(layout.createSequentialGroup()
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING)
                            .addComponent(jScrollPane1, javax.swing.GroupLayout.Alignment.LEADING, javax.swing.GroupLayout.DEFAULT_SIZE, 425, Short.MAX_VALUE)
                            .addGroup(javax.swing.GroupLayout.Alignment.LEADING, layout.createSequentialGroup()
                                .addComponent(jLabel1)
                                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                                .addComponent(cbb_database, javax.swing.GroupLayout.PREFERRED_SIZE, 125, javax.swing.GroupLayout.PREFERRED_SIZE)))
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING)
                            .addComponent(bt_excute, javax.swing.GroupLayout.PREFERRED_SIZE, 65, javax.swing.GroupLayout.PREFERRED_SIZE)
                            .addComponent(bt_save, javax.swing.GroupLayout.PREFERRED_SIZE, 65, javax.swing.GroupLayout.PREFERRED_SIZE)
                            .addComponent(bt_cancel))))
                .addContainerGap())
        );
        layout.setVerticalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(jLabel1)
                    .addComponent(cbb_database, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addGroup(layout.createSequentialGroup()
                        .addComponent(bt_excute)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addComponent(bt_save)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addComponent(bt_cancel))
                    .addComponent(jScrollPane1, javax.swing.GroupLayout.PREFERRED_SIZE, 135, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(jTabbedPane1, javax.swing.GroupLayout.DEFAULT_SIZE, 120, Short.MAX_VALUE)
                .addContainerGap())
        );
    }// </editor-fold>//GEN-END:initComponents

private void bt_excuteActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_bt_excuteActionPerformed
    String[] query = sqlquerytext.getText().split("\\n");
    List<String> lstQuery = new ArrayList<String>();
    
    tableResult.setColumnCount(0);
    tableResult.setRowCount(0);
    
    for(int i = 0; i < query.length; i++){
        lstQuery.add(query[i]);
    }
    queryDTO.setDatabaseName((String)cbb_database.getItemAt(cbb_database.getSelectedIndex()));   
    queryDTO.setQuerySentences(lstQuery);
    
    try {
        execute = new Thread(new ExecuteThread(socket, this, oos, ois));
        execute.start();
    } catch (IOException ex) {
        Logger.getLogger(DBClientQuery.class.getName()).log(Level.SEVERE, null, ex);
    }
}//GEN-LAST:event_bt_excuteActionPerformed

private void bt_cancelActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_bt_cancelActionPerformed
// TODO add your handling code here:
    tabpane.remove(tabpane.getSelectedIndex());
}//GEN-LAST:event_bt_cancelActionPerformed

private void bt_saveActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_bt_saveActionPerformed
// TODO add your handling code here:
   List<String> querySentences = new ArrayList<String>();
    QueryDTO queryDTO = new QueryDTO();
    
    queryDTO.setDatabaseName((String)cbb_database.getSelectedItem());
    String query[] = sqlquerytext.getText().split("\\n");
    
    for(int i = 0; i < query.length; i++){
        querySentences.add(query[i]);
    }
    
    queryDTO.setQuerySentences(querySentences); 
    
    if(savePath.compareTo("") == 0){
        JFileChooser fc = new JFileChooser();    
        int returnVal = fc.showSaveDialog(this);
        if (returnVal == JFileChooser.APPROVE_OPTION) { 
            savePath = fc.getSelectedFile().toString() + ".xml";
            try {
                queryBUS.saveQuery(queryDTO, savePath);
            } catch (ParserConfigurationException ex) {
                Logger.getLogger(DBClientQuery.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }else{
        try {
            queryBUS.saveQuery(queryDTO, savePath);
        } catch (ParserConfigurationException ex) {
            Logger.getLogger(DBClientQuery.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}//GEN-LAST:event_bt_saveActionPerformed

    // Variables declaration - do not modify//GEN-BEGIN:variables
    private javax.swing.JButton bt_cancel;
    private javax.swing.JButton bt_excute;
    private javax.swing.JButton bt_save;
    private javax.swing.JComboBox cbb_database;
    private javax.swing.JLabel jLabel1;
    private javax.swing.JScrollPane jScrollPane1;
    private javax.swing.JScrollPane jScrollPane2;
    private javax.swing.JScrollPane jScrollPane3;
    private javax.swing.JTabbedPane jTabbedPane1;
    private javax.swing.JTextArea messagestext;
    private javax.swing.JTable resulttable;
    private javax.swing.JTextArea sqlquerytext;
    // End of variables declaration//GEN-END:variables
}
